package com.bringspring.common.database.config;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.bringspring.common.database.model.DataSourceModel;
import com.bringspring.common.database.model.DbTableFieldModel;
import com.bringspring.common.database.model.DbTableModel;
import com.bringspring.common.database.model.dto.DbConnDTO;
import com.bringspring.common.database.model.dto.PreparedStatementDTO;
import com.bringspring.common.database.model.interfaces.JdbcGetMod;
import com.bringspring.common.database.sql.SqlBase;
import com.bringspring.common.database.util.ConnUtil;
import com.bringspring.common.database.util.DbTypeUtil;
import com.bringspring.common.database.util.JdbcUtil;
import com.bringspring.common.exception.DataException;
import com.bringspring.common.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.reflections.Reflections;
import org.reflections.scanners.TypeAnnotationsScanner;
import org.reflections.util.ClasspathHelper;
import org.reflections.util.ConfigurationBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.lang.reflect.Field;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 数据库初始化
 *
 * @author 开发平台组
 * @version V1.0.0
 * @copyright 股份有限公司
 * @date 2021/11/13 8:49
 */
@Slf4j
@Configuration
@ComponentScan("com.bringspring")
public class DbTableConfig {


    @Value("${config.initTable:false}")
    private boolean initTable;

    @Value("${config.SoftVersion}")
    private String softVersion;

    @Value("${config.sqlFile}")
    private String sqlFile;

    @Autowired
    private DataSourceModel dataSourceModel;

    /**
     * 对接数据库的实体层
     */
    static final String ALIASES_PACKAGE = "com.bringspring.**.entity";


    /**
     * 处理业务逻辑
     */
    @Bean
    public void initDbTable() throws Exception {
        if (initTable) {

            //设置数据源信息
            DbConnDTO connDTO = setResource(dataSourceModel);
            //获取数据库下所有表信息
            List<DbTableModel> tableModelList = getListCommon(null, DbTableModel.class, connDTO);
            if (tableModelList.size() < 1) {
                //数据库全部初始化
                this.initDbInfo(setResource(dataSourceModel));
            } else {
                //判断版本号配置文件中的和数据库的作对比
                //得到数据库里面的版本信息
                String nowVersion = this.getVersion(setResource(dataSourceModel));
                //按照现有版本号先进行处理，后续版本号规则确定后进一步处理
                int result = 0;
                try {
                    result = this.compareVersions(softVersion.substring(0, softVersion.indexOf("-")), nowVersion.substring(1));
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
                if (result > 0) {
                    //校验对比实体字段和数据库现有表和字段
                    //得到项目中全部实体信息
                    List<Class<?>> classes = scanClasses();
                    for (Class clazz : classes) {
                        TableName tableNameAnnotation = (TableName) clazz.getAnnotation(TableName.class);
                        // 获取注解的值
                        String tableName = tableNameAnnotation.value().toUpperCase();
                        //循环数据库中得到的表信息
                        List<String> tableList = new ArrayList<>();
                        for (DbTableModel tableModel : tableModelList) {
                            tableList.add(tableModel.getTable().toUpperCase());
                        }
                        //得到实体类字段信息
                        Field[] fields = clazz.getDeclaredFields();
                        //获取父级字段信息
                        Field[] superFields = clazz.getSuperclass().getDeclaredFields();
                        Field[] resultFields = new Field[fields.length + superFields.length];
                        System.arraycopy(fields, 0, resultFields, 0, fields.length);
                        System.arraycopy(superFields, 0, resultFields, fields.length, superFields.length);
                        boolean tableContains = tableList.contains(tableName);
                        if (tableContains) {
                            //获取数据源库表下的列信息
                            List<DbTableFieldModel> listCommon = getListCommon(tableName, DbTableFieldModel.class, connDTO);
                            List<String> fieldList = new ArrayList<>();
                            List<Map<String, String>> idList = new ArrayList<>();
                            List<Map<String, String>> newFieldList = new ArrayList<>();
                            for (DbTableFieldModel fieldModel : listCommon) {
                                fieldList.add(fieldModel.getField().toUpperCase());
                            }
                            for (Field field : resultFields) {
                                String fieldStr = "";
                                String id = "";
                                Map<String, String> fieldMap = new HashMap<>();
                                Map<String, String> idMap = new HashMap<>();
                                if (field.isAnnotationPresent(TableId.class)) {
                                    TableId tableIdAnnotation = field.getAnnotation(TableId.class);
                                    id = tableIdAnnotation.value().toUpperCase();
                                    if (StringUtils.isNotEmpty(id)) {
                                        boolean contains = fieldList.contains(id);
                                        if (!contains) {
                                            idMap.put("id", id);
                                            idMap.put("fieldType", field.getType().toString());
                                            idList.add(idMap);
                                        }
                                    }
                                }
                                if (field.isAnnotationPresent(TableField.class)) {
                                    TableField tableFieldAnnotation = field.getAnnotation(TableField.class);
                                    fieldStr = tableFieldAnnotation.value().toUpperCase();
                                    if (StringUtils.isNotEmpty(fieldStr)) {
                                        boolean contains = fieldList.contains(fieldStr);
                                        if (!contains) {
                                            fieldMap.put("fieldStr", fieldStr);
                                            fieldMap.put("fieldType", field.getType().toString());
                                            newFieldList.add(fieldMap);
                                        }
                                    }

                                }
                            }
                            if (idList.size() > 0 || newFieldList.size() > 0) {
                                this.updateCreatField(tableName, idList, newFieldList, setResource(dataSourceModel));
                            }

                        } else {
                            //表不存在直接创建
                            Map<String, String> tableMap = new HashMap();
                            List<Map<String, String>> fieldList = new ArrayList<>();
                            List<Map<String, String>> newTableList = new ArrayList<>();
                            //封装字段数据
                            for (Field field : resultFields) {
                                Map<String, String> fieldMap = new HashMap<>();
                                Map<String, String> newTableMap = new HashMap<>();
                                String fieldStr = "";
                                String id = "";
                                if (field.isAnnotationPresent(TableId.class)) {
                                    TableId tableIdAnnotation = field.getAnnotation(TableId.class);
                                    id = tableIdAnnotation.value().toUpperCase();
                                    if (StringUtils.isNotEmpty(id)) {
                                        newTableMap.put("id", id);
                                        newTableMap.put("fieldType", field.getType().toString());
                                        newTableList.add(newTableMap);
                                    }

                                }
                                if (field.isAnnotationPresent(TableField.class)) {
                                    TableField tableFieldAnnotation = field.getAnnotation(TableField.class);
                                    fieldStr = tableFieldAnnotation.value().toUpperCase();
                                    if (StringUtils.isNotEmpty(fieldStr)) {
                                        fieldMap.put("fieldStr", fieldStr);
                                        fieldMap.put("fieldType", field.getType().toString());
                                        fieldList.add(fieldMap);
                                    }

                                }

                            }
                            tableMap.put("tableName", tableName);
                            this.addCreatField(tableMap, newTableList, fieldList, setResource(dataSourceModel));
                        }
                    }
                }

            }
        }

    }

    /**
     * 创建表
     *
     * @param dataSourceType
     * @param tableMap
     * @return
     */
    private String CreatTableSqlString(String dataSourceType, List<Map<String, String>> newTableList, Map<String, String> tableMap, List<Map<String, String>> fieldList) {
        StringBuffer stringBuffer = new StringBuffer();
        if (dataSourceType.contains("MySQL")) {
            //解析页面入参，拼接创建表sql语句
            //mysql
            //CREATE table IF NOT EXISTS STUDENTS (id INTEGER not NULL,name VARCHAR(255),age INTEGER,sex INTEGER, PRIMARY KEY ( id ));
            stringBuffer.append("CREATE table  `").append(tableMap.get("tableName").toString()).append("` ( `");
            String primary = "";
            //循环拼接主键
            for (int i = 0; i < newTableList.size(); i++) {
                Map<String, String> newTabledMap = newTableList.get(i);
                if (StringUtils.isNotEmpty(newTabledMap.get("id"))) {
                    if (newTabledMap.get("fieldType").toUpperCase().contains("STRING")) {
                        //长度不能为空
                        stringBuffer.append(newTabledMap.get("id")).append("` ").append(" VARCHAR(255) NOT NULL , ");
                    } else if (newTabledMap.get("fieldType").toUpperCase().contains("INT") || newTabledMap.get("fieldType").toUpperCase().contains("LONG") ||
                            newTabledMap.get("fieldType").toUpperCase().contains("BIGDECIMAL")) {
                        stringBuffer.append(newTabledMap.get("id")).append("` INT NOT NULL , ");
                    } else if (newTabledMap.get("fieldType").toUpperCase().contains("DATE")) {
                        stringBuffer.append(newTabledMap.get("id")).append("` DATETIME NOT NULL , ");
                    } else {
                        stringBuffer.append(newTabledMap.get("id")).append("` ").append(" TEXT NOT NULL , ");
                    }
                    if (i < newTableList.size() - 1) {
                        stringBuffer.append("`");

                    }
                    //是主键
                    if (i == 0) {
                        primary = "PRIMARY KEY (";
                    }
                    if (i == newTableList.size() - 1) {
                        primary += "`" + newTabledMap.get("id") + "`)";
                    } else {
                        primary += "`" + newTabledMap.get("id") + "` ,";
                    }

                }
            }
            //循环拼接字段
            for (int i = 0; i < fieldList.size(); i++) {
                Map<String, String> fieldMap = fieldList.get(i);
                if (i == 0) {
                    stringBuffer.append("`");
                }
                if (fieldMap.get("fieldType").toUpperCase().contains("STRING")) {
                    //长度不能为空
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` ").append(" VARCHAR(255) NULL , ");
                } else if (fieldMap.get("fieldType").toUpperCase().contains("INT") || fieldMap.get("fieldType").toUpperCase().contains("LONG") ||
                        fieldMap.get("fieldType").toUpperCase().contains("BIGDECIMAL")) {
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` INT NULL , ");
                } else if (fieldMap.get("fieldType").toUpperCase().contains("DATE")) {
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` DATETIME NULL , ");
                } else {
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` ").append(" TEXT NULL , ");
                }
                if (i < fieldList.size() - 1) {
                    stringBuffer.append("`");
                }

            }

            stringBuffer.append(primary).append(")");
        } else if (dataSourceType.contains("Oracle")) {

        } else if (dataSourceType.contains("PostgreSQL")) {

        } else if (dataSourceType.contains("SQLServer")) {

        }

        return stringBuffer.toString();
    }

    private void initDbInfo(DbConnDTO connDTO) {
        Reader reader = null;
        try {
            // 通过 JDBC 获取数据库连接 connection
            Connection connection = connDTO.getConn();
            // 创建 ScriptRunner 对象
            ScriptRunner scriptRunner = new ScriptRunner(connection);
            // 设置自动提交选项
            scriptRunner.setAutoCommit(false);
            // 设置出错停止选项
            scriptRunner.setStopOnError(true);
            // 指定 SQL 文件路径
            File file = new File(sqlFile);
            // 创建 FileReader 对象来读取文件内容
            reader = new FileReader(file);
            // 使用 ScriptRunner 执行 SQL 脚本
            scriptRunner.runScript(reader);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                reader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 扫描所有实体类
     *
     * @return
     */
    private static List<Class<?>> scanClasses() {
        List<Class<?>> classList = new ArrayList<>();
        new ConfigurationBuilder()
                .setScanners(new TypeAnnotationsScanner())
                .setUrls(ClasspathHelper.forPackage(ALIASES_PACKAGE));
        org.reflections.Configuration config = ConfigurationBuilder
                .build();
        org.reflections.Configuration config1 = new ConfigurationBuilder();
        for (URL url : config.getUrls()) {
            if (url.toString().contains("bringspring") || url.toString().contains("classes")) {
                config1.getUrls().add(url);
            }
        }
        Reflections reflections = new Reflections(config1);
        Set<Class<?>> allClasses = reflections.getTypesAnnotatedWith(TableName.class);
        classList.addAll(allClasses);
        return classList;
    }

    /**
     * 设置数据源
     *
     * @param dataSourceModel
     * @throws DataException ignore
     */
    private DbConnDTO setResource(DataSourceModel dataSourceModel) throws DataException {

        try {
            return new DbConnDTO(DbTypeUtil.getDb(dataSourceModel),
                    dataSourceModel, ConnUtil.getConn(dataSourceModel));
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
    }

    /**
     * =======================jdbc查询========================
     **/

    private <T extends JdbcGetMod> List<T> getListCommon(String table, Class<T> modType, DbConnDTO connDTO) throws DataException {
        try {
            Connection conn = connDTO.getConn();
            //获取查询sql语句
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            PreparedStatementDTO preparedStatementDTO = null;
            if (modType == DbTableModel.class) {
                preparedStatementDTO = sqlBase.getTableListPSD(conn, connDTO.getDbSource());
            } else {
                preparedStatementDTO = sqlBase.getFieldListPSD(conn, table, connDTO.getDbSource());
            }
            //根据sql语句获取List
            return JdbcUtil.queryCustomMods(preparedStatementDTO, modType);
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
    }

    /**
     * 新增字段
     *
     * @param connDTO
     * @throws Exception
     */
    private void updateCreatField(String tableName, List<Map<String, String>> idList, List<Map<String, String>> newFieldList, DbConnDTO connDTO) throws Exception {

        //创建JDBC链接
        Connection conn = connDTO.getConn();
        if (conn != null) {
            Statement stmt = conn.createStatement();
            String sql = this.addFieldSqlString(connDTO.getDbSource().getDbType(), tableName, idList, newFieldList);
            log.info("新增字段要执行的sql---" + sql);
            stmt.executeUpdate(sql);
            stmt.close();
            conn.close();
        } else {
            throw new Exception("请检查数据源链接配置");
        }
    }

    /**
     * 创建表
     *
     * @param connDTO
     * @throws Exception
     */
    private void addCreatField(Map<String, String> tableMap, List<Map<String, String>> newTableList, List<Map<String, String>> fieldList, DbConnDTO connDTO) throws Exception {

        //创建JDBC链接
        Connection conn = connDTO.getConn();
        if (conn != null) {
            Statement stmt = conn.createStatement();
            String sql = this.CreatTableSqlString(connDTO.getDbSource().getDbType(), newTableList, tableMap, fieldList);
            log.info("创建表要执行的sql---" + sql);
            stmt.executeUpdate(sql);
            stmt.close();
            conn.close();
        } else {
            throw new Exception("请检查数据源链接配置");
        }
    }

    /**
     * 新增字段sql
     *
     * @param dataSourceType
     * @param
     * @return
     */
    private String addFieldSqlString(String dataSourceType, String tableName, List<Map<String, String>> idList, List<Map<String, String>> newFieldList) {
        StringBuffer stringBuffer = new StringBuffer();
        if (dataSourceType.contains("MySQL") || dataSourceType.contains("Oracle")) {
            //解析页面入参，拼接创建表sql语句
            //mysql
            //CREATE table IF NOT EXISTS STUDENTS (id INTEGER not NULL,name VARCHAR(255),age INTEGER,sex INTEGER, PRIMARY KEY ( id ));
            stringBuffer.append("ALTER TABLE  `").append(tableName);

            String primary = "";
            //循环拼接主键  decimal
            for (int i = 0; i < idList.size(); i++) {
                Map<String, String> newTabledMap = idList.get(i);

                if (i == 0) {
                    stringBuffer.append("` ADD `");
                } else {
                    stringBuffer.append(" ADD `");
                }
                if (StringUtils.isNotEmpty(newTabledMap.get("id"))) {

                    if (newTabledMap.get("fieldType").toUpperCase().contains("STRING")) {
                        //长度不能为空
                        stringBuffer.append(newTabledMap.get("id")).append("` ").append(" VARCHAR(255) NOT NULL ,");
                    } else if (newTabledMap.get("fieldType").toUpperCase().contains("INT") || newTabledMap.get("fieldType").toUpperCase().contains("LONG") ||
                            newTabledMap.get("fieldType").toUpperCase().contains("BIGDECIMAL")) {
                        stringBuffer.append(newTabledMap.get("id")).append("` INT NOT NULL ,");
                    } else if (newTabledMap.get("fieldType").toUpperCase().contains("DATE")) {
                        stringBuffer.append(newTabledMap.get("id")).append("` DATETIME NOT NULL ,");
                    } else {
                        stringBuffer.append(newTabledMap.get("id")).append("` ").append(" TEXT NOT NULL ,");
                    }

                    //是主键（原表如果不存在主键会报错，先不考虑）
                    if (i == 0) {
                        primary = "DROP PRIMARY KEY, ADD PRIMARY KEY (";
                    }
                    if (i == idList.size() - 1) {
                        primary += "`" + newTabledMap.get("id") + "`)";
                    } else {
                        primary += "`" + newTabledMap.get("id") + "` ,";
                    }

                }
            }
            //循环拼接字段
            for (int i = 0; i < newFieldList.size(); i++) {
                Map<String, String> fieldMap = newFieldList.get(i);
                if (i == 0) {
                    if (idList.size() < 1) {
                        stringBuffer.append("` ADD `");
                    } else {
                        stringBuffer.append(" ADD `");
                    }
                } else {
                    stringBuffer.append(" ADD `");
                }
                if (fieldMap.get("fieldType").toUpperCase().contains("STRING")) {
                    //长度不能为空
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` ").append(" VARCHAR(255) NULL ");
                } else if (fieldMap.get("fieldType").toUpperCase().contains("INT") || fieldMap.get("fieldType").toUpperCase().contains("LONG") ||
                        fieldMap.get("fieldType").toUpperCase().contains("BIGDECIMAL")) {
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` INT NULL ");
                } else if (fieldMap.get("fieldType").toUpperCase().contains("DATE")) {
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` DATETIME NULL ");
                } else {
                    stringBuffer.append(fieldMap.get("fieldStr")).append("` ").append(" TEXT NULL ");
                }
                if (idList.size() > 0) {
                    stringBuffer.append(" ,");
                } else if (i < newFieldList.size() - 1) {
                    stringBuffer.append(" ,");

                }
            }

            stringBuffer.append(primary);


//                //是主键
//                stringBuffer.append(", DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`)");

        } else if (dataSourceType.contains("PostgreSQL") || dataSourceType.contains("SQLServer")) {

        }

        return stringBuffer.toString();
    }

    /**
     * 得到版本信息
     *
     * @param connDTO
     * @throws Exception
     */
    private String getVersion(DbConnDTO connDTO) throws Exception {
        String nowVersion = "";
        Connection conn = null;
        Statement stmt = null;
        try {
            //统一转换为JDBC模式
            LinkedHashMap<String, String> resultMap = new LinkedHashMap<String, String>();
            //创建JDBC链接
            conn = connDTO.getConn();
            if (conn != null) {
                stmt = conn.createStatement();
                String sql = "select key_value from base_system_config where key_name = 'sysVersion'";
                ResultSet rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    nowVersion = rs.getString("key_value");
                }

                stmt.close();
                conn.close();

            } else {
                throw new Exception("请检查数据源链接配置");
            }

        } catch (Exception e) {
            throw new Exception("系统异常");
        } finally {
            stmt.close();
            conn.close();
        }
        return nowVersion;

    }

    private int compareVersions(String version1, String version2) {
        // 使用点号 "." 进行拆分
        String[] v1 = version1.split("\\.");
        String[] v2 = version2.split("\\.");
        // 取版本号中部分数量的最大值
        int length = Math.max(v1.length, v2.length);

        for (int i = 0; i < length; i++) {
            // 若版本号中没有这个部分，则默认为0
            int num1 = i < v1.length ? Integer.parseInt(v1[i]) : 0;
            int num2 = i < v2.length ? Integer.parseInt(v2[i]) : 0;

            if (num1 < num2) {
                // 第一个版本号小于第二个版本号
                return -1;
            } else if (num1 > num2) {
                // 第一个版本号大于第二个版本号
                return 1;
            }
        }
        // 两个版本号相等
        return 0;
    }

}
